{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Python交流群\n",
    "#QQ:754807407"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import xlwings as xw\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "#文件路径\n",
    "final_form = Path(r'D:\\Working\\私活\\数据清洗\\21-12-30表格清洗需求\\穿透管理输出表v3终版.xlsx')\n",
    "folder_path =  Path(r'D:\\Working\\私活\\数据清洗\\21-12-30表格清洗需求\\输入结果')\n",
    "qiong_form = Path(r'D:\\Working\\私活\\数据清洗\\基金穷举v4.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 提前读取输出结果的三个表，用于填充数据\n",
    "df_final1 = pd.read_excel(final_form,header=[0,1]).iloc[:19,:6]\n",
    "df_final2 = pd.read_excel(final_form,header=[22,23]).iloc[:16,:]\n",
    "df_final3 = pd.read_excel(final_form,header=[41,42])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "#读取各表数据\n",
    "file_list = folder_path.glob('输入*.xls')\n",
    "pathlist = list(file_list)\n",
    "df1 = pd.read_excel(pathlist[2],header=4).iloc[1:,:]\n",
    "df2 = pd.read_excel(pathlist[3],header=4).iloc[2:,:]\n",
    "df3 = pd.read_excel(pathlist[4],header=1)\n",
    "df4 = pd.read_excel(pathlist[5],header=3)\n",
    "df4 = df4.drop(index=[df4.shape[0]-1,df4.shape[0]-2])\n",
    "df5 = pd.read_excel(pathlist[6],header=4).iloc[2:,:]\n",
    "df6 = pd.read_excel(pathlist[7],header=4).iloc[2:,:]\n",
    "df7 = pd.read_excel(pathlist[8],header=3)\n",
    "df7 = df7.drop(index=df7.shape[0]-1)\n",
    "df9 = pd.read_excel(pathlist[9],header=3)\n",
    "df10 = pd.read_excel(pathlist[0],header=3).iloc[1:,:]\n",
    "df11 = pd.read_excel(pathlist[1],header=3).iloc[1:,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_wash1(df_form,df_final,ori_col,form_col,form_col_eng,capital1,capitalP,value1,valueP,s_value):\n",
    "    l = []\n",
    "    y = []\n",
    "    j = []\n",
    "    q = []\n",
    "    z = []\n",
    "    for i in range(df_final.shape[0]):\n",
    "        df2 = df_form[df_form[ori_col]==df_final.loc[i,form_col][form_col_eng]]\n",
    "        if i<5:\n",
    "            list1 = df2[capital1].tolist()\n",
    "            if len(list1)==0:\n",
    "                list1.append(np.nan)\n",
    "            list2 = df2[capitalP].tolist()\n",
    "            if len(list2)==0:\n",
    "                list2.append(np.nan)\n",
    "            list3 = df2[value1].tolist()\n",
    "            if len(list3)==0:\n",
    "                list3.append(np.nan)\n",
    "            list4 = df2[valueP].tolist()\n",
    "            if len(list4)==0:\n",
    "                list4.append(np.nan)\n",
    "            l.append(list1[0])\n",
    "            y.append(list2[0])\n",
    "            j.append(list3[0])\n",
    "            q.append(list4[0])\n",
    "        if i >=5:\n",
    "            list5 = df2[s_value].tolist()\n",
    "            if len(list5)==0:\n",
    "                list5.append(np.nan)\n",
    "            z.append(list5[0])\n",
    "    return l,y,j,q,z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_wash2(df_form,df_final,ori_col,form_col,form_col_eng,capital1,capitalP,value1,valueP):\n",
    "    l = []\n",
    "    y = []\n",
    "    j = []\n",
    "    q = []\n",
    "    for i in range(df_final.shape[0]):\n",
    "        df2 = df_form[df_form[ori_col]==df_final.loc[i,form_col][form_col_eng]]\n",
    "        list1 = df2[capital1].tolist()\n",
    "        if len(list1)==0:\n",
    "            list1.append(np.nan)\n",
    "        list2 = df2[capitalP].tolist()\n",
    "        if len(list2)==0:\n",
    "            list2.append(np.nan)\n",
    "        list3 = df2[value1].tolist()\n",
    "        if len(list3)==0:\n",
    "            list3.append(np.nan)\n",
    "        list4 = df2[valueP].tolist()\n",
    "        if len(list4)==0:\n",
    "            list4.append(np.nan)\n",
    "        l.append(list1[0])\n",
    "        y.append(list2[0])\n",
    "        j.append(list3[0])\n",
    "        q.append(list4[0])\n",
    "    return l,y,j,q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_wash3(df_form,df_final,ori_col,form_col,form_col_eng,capital1,capitalP,value1,valueP,s_value):\n",
    "    l = []\n",
    "    y = []\n",
    "    j = []\n",
    "    q = []\n",
    "    z = []\n",
    "    df_form[ori_col] = df_form[ori_col].str.replace(':','')\n",
    "    for i in range(df_final.shape[0]): \n",
    "        df2 = df_form[df_form[ori_col]==df_final.loc[i,form_col][form_col_eng]]\n",
    "        if i<5:\n",
    "            list1 = df2[capital1].tolist()\n",
    "            if len(list1)==0:\n",
    "                list1.append(np.nan)\n",
    "            if capitalP.find('%')!=-1:\n",
    "                list2 = (df2[capitalP]*0.01).tolist()\n",
    "                if len(list2)==0:\n",
    "                    list2.append(np.nan)\n",
    "            else:\n",
    "                list2 = df2[capitalP].tolist()\n",
    "                if len(list2)==0:\n",
    "                    list2.append(np.nan)\n",
    "            list3 = df2[value1].tolist()\n",
    "            if len(list3)==0:\n",
    "                list3.append(np.nan)\n",
    "            if valueP.find('%')!=-1:\n",
    "                list4 = (df2[valueP]*0.01).tolist()\n",
    "                if len(list4)==0:\n",
    "                    list4.append(np.nan)\n",
    "            else:\n",
    "                list4 = df2[valueP].tolist()\n",
    "                if len(list4)==0:\n",
    "                    list4.append(np.nan)\n",
    "            l.append(list1[0])\n",
    "            y.append(list2[0])\n",
    "            j.append(list3[0])\n",
    "            q.append(list4[0])\n",
    "        if i >=5:\n",
    "            list5 = df2[s_value].tolist()\n",
    "            if len(list5)==0:\n",
    "                list5.append(np.nan)\n",
    "            z.append(list5[0])\n",
    "    return l,y,j,q,z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_wash4(df_form,df_final,ori_col,form_col,form_col_eng,capital1,capitalP,value1,valueP):\n",
    "    l = []\n",
    "    y = []\n",
    "    j = []\n",
    "    q = []\n",
    "    df_form[ori_col] = df_form[ori_col].str.replace(':','')\n",
    "    for i in range(df_final.shape[0]):\n",
    "        df2 = df_form[df_form[ori_col]==df_final.loc[i,form_col][form_col_eng]]\n",
    "        list1 = df2[capital1].tolist()\n",
    "        if len(list1)==0:\n",
    "            list1.append(np.nan)\n",
    "        if capitalP.find('%')!=-1:\n",
    "                list2 = (df2[capitalP]*0.01).tolist()\n",
    "                if len(list2)==0:\n",
    "                    list2.append(np.nan)\n",
    "        else:\n",
    "            list2 = df2[capitalP].tolist()\n",
    "            if len(list2)==0:\n",
    "                list2.append(np.nan)\n",
    "        list3 = df2[value1].tolist()\n",
    "        if len(list3)==0:\n",
    "            list3.append(np.nan)\n",
    "        if valueP.find('%')!=-1:\n",
    "                list4 = (df2[valueP]*0.01).tolist()\n",
    "                if len(list4)==0:\n",
    "                    list4.append(np.nan)\n",
    "        else:\n",
    "            list4 = df2[valueP].tolist()\n",
    "            if len(list4)==0:\n",
    "                list4.append(np.nan)\n",
    "        l.append(list1[0])\n",
    "        y.append(list2[0])\n",
    "        j.append(list3[0])\n",
    "        q.append(list4[0])\n",
    "    return l,y,j,q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form1(df1,df_final1,df_final2,df_final3):\n",
    "    df_deal= df1[df1['科目代码'].str.startswith('1103')&df1['单位成本'].notnull()]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占比']\n",
    "    df_final3['市值']=df_deal['市值']\n",
    "    df_final3['市值占比']=df_deal['市值占比']\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3['行情']=df_deal['行情']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='D:E',header=2).iloc[:16,:]\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='D:E',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'（二）基金信息.1':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(1,df1.shape[0]):\n",
    "        if df1.loc[i,'科目名称'] in list(df_qiong1['科目名称.1']):\n",
    "            index1 = list(df_qiong1['科目名称.1']).index(df1.loc[i,'科目名称'])\n",
    "            df1.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(1,df1.shape[0]):\n",
    "        if df1.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df1.loc[i,'科目代码'])\n",
    "            df1.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "    #处理表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #处理表2\n",
    "    df_final2['账本成本'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比',)[0]\n",
    "    df_final2['账本成本占比'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[1]\n",
    "    df_final2['账本市值'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[2]\n",
    "    df_final2['账本市值占比'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #输出\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form1.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()\n",
    "    \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form2(df2,df_final1,df_final2,df_final3):\n",
    "    #生成表3\n",
    "    df_deal= df2[df2['科目代码'].str.startswith('1103')&df2['单位成本'].notnull()]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占比']\n",
    "    df_final3['市值']=df_deal['市值']\n",
    "    df_final3['市值占比']=df_deal['市值占比']\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3['行情']=df_deal['行情']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='G:H',header=2).iloc[:16,:]\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='G:H',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'（二）基金信息.2':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(2,df2.shape[0]+2):\n",
    "        if df2.loc[i,'科目名称'] in list(df_qiong1['科目名称.2']):\n",
    "            index1 = list(df_qiong1['科目名称.2']).index(df2.loc[i,'科目名称'])\n",
    "            df2.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(2,df2.shape[0]+2):\n",
    "        if df2.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df2.loc[i,'科目代码'])\n",
    "            df2.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "    #生成表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash1(df1,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[0]\n",
    "    df_final2['账本成本占比'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[1]\n",
    "    df_final2['账本市值'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[2]\n",
    "    df_final2['账本市值占比'] = data_wash2(df1,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #写入文件\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form2.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form3(df3,df_final1,df_final2,df_final3):\n",
    "    #生成表3：\n",
    "    df_deal= df3[df3['科目代码'].str.startswith('1103')&(df3['单位成本']>0.0)]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占净值%']*0.01\n",
    "    df_final3['市值']=df_deal['市值']\n",
    "    df_final3['市值占比']=df_deal['市值占净值%']*0.01\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #穷举\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='J:K',header=2).iloc[:16,:]\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='J:K',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'（二）基金信息.3':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(1,df3.shape[0]):\n",
    "        if df3.loc[i,'科目名称'] in list(df_qiong1['科目名称.3']):\n",
    "            index1 = list(df_qiong1['科目名称.3']).index(df3.loc[i,'科目名称'])\n",
    "            df3.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(1,df3.shape[0]):\n",
    "        if df3.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df3.loc[i,'科目代码'])\n",
    "            df3.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "    #生成表1：\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash3(df3,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash3(df3,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash3(df3,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash3(df3,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash3(df3,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)   \n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash4(df3,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[0]\n",
    "    df_final2['账本成本占比'] = data_wash4(df3,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[1]\n",
    "    df_final2['账本市值'] = data_wash4(df3,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[2]\n",
    "    df_final2['账本市值占比'] = data_wash4(df3,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #写入表\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form3.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form4(df4,df_final1,df_final2,df_final3):\n",
    "    #生成表3\n",
    "    df_deal= df4[df4['科目代码'].str.startswith('1103')&(df4['单位成本']>0.0)]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占净值%']*0.01\n",
    "    df_final3['市值']=df_deal['市值']\n",
    "    df_final3['市值占比']=df_deal['市值占净值%']*0.01\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='M:N',header=2).iloc[:16,:]\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='M:N',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'（二）基金信息.4':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(1,df4.shape[0]):\n",
    "        if df4.loc[i,'科目名称'] in list(df_qiong1['科目名称.4']):\n",
    "            index1 = list(df_qiong1['科目名称.4']).index(df4.loc[i,'科目名称'])\n",
    "            df4.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(1,df4.shape[0]):\n",
    "        if df4.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df4.loc[i,'科目代码'])\n",
    "            df4.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "     #生成表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash3(df4,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash3(df4,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash3(df4,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash3(df4,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash3(df4,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash4(df4,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[0]\n",
    "    df_final2['账本成本占比'] = data_wash4(df4,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[1]\n",
    "    df_final2['账本市值'] = data_wash4(df4,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[2]\n",
    "    df_final2['账本市值占比'] = data_wash4(df4,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #生成结果表\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form4.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form5(df5,df_final1,df_final2,df_final3):\n",
    "    df_deal= df5[df5['科目代码'].str.startswith('1103')&df5['单位成本'].notnull()]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占比']\n",
    "    df_final3['市值']=df_deal['市值']\n",
    "    df_final3['市值占比']=df_deal['市值占比']\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3['行情']=df_deal['行情']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='P:Q',header=2).iloc[:16,:]\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='P:Q',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'（二）基金信息.5':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(2,df5.shape[0]+2):\n",
    "        if df5.loc[i,'科目名称'] in list(df_qiong1['科目名称.5']):\n",
    "            index1 = list(df_qiong1['科目名称.5']).index(df5.loc[i,'科目名称'])\n",
    "            df5.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(2,df5.shape[0]+2):\n",
    "        if df5.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df5.loc[i,'科目代码'])\n",
    "            df5.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "    #生成表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash3(df5,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash3(df5,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash3(df5,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash3(df5,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash3(df5,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash2(df5,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比',)[0]\n",
    "    df_final2['账本成本占比'] = data_wash2(df5,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[1]\n",
    "    df_final2['账本市值'] = data_wash2(df5,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[2]\n",
    "    df_final2['账本市值占比'] = data_wash2(df5,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #输出结果\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form5.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form6(df6,df_final1,df_final2,df_final3):\n",
    "    #生成表3\n",
    "    df_deal= df6[df6['科目代码'].str.startswith('1103')&df6['单位成本'].notnull()]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占比']\n",
    "    df_final3['市值']=df_deal['市值']\n",
    "    df_final3['市值占比']=df_deal['市值占比']\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3['行情']=df_deal['行情']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='S:T',header=2).iloc[:16,:]\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='S:T',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'（二）基金信息.6':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(2,df6.shape[0]+2):\n",
    "        if df6.loc[i,'科目名称'] in list(df_qiong1['科目名称.6']):\n",
    "            index1 = list(df_qiong1['科目名称.6']).index(df6.loc[i,'科目名称'])\n",
    "            df6.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "            \n",
    "    #处理科目代码    \n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(1,df1.shape[0]):\n",
    "        if df1.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df1.loc[i,'科目代码'])\n",
    "            df1.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "\n",
    "\n",
    "    #生成表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash3(df6,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash3(df6,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash3(df6,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash3(df6,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash3(df6,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占比','市值','市值占比','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash4(df6,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比',)[0]\n",
    "    df_final2['账本成本占比'] = data_wash4(df6,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[1]\n",
    "    df_final2['账本市值'] = data_wash4(df6,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[2]\n",
    "    df_final2['账本市值占比'] = data_wash4(df6,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占比','市值','市值占比')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #生成结果文件\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form6.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form7(df7,df_final1,df_final2,df_final3):\n",
    "#生成表3\n",
    "    df_deal= df7[df7['科目代码'].str.startswith('1103')&(df7['单位成本']>0.0)]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占净值%']*0.01\n",
    "    df_final3['市值']=df_deal['市值']\n",
    "    df_final3['市值占比']=df_deal['市值占净值%']*0.01\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='V:W',header=2).iloc[:16,:]\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='V:W',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'（二）基金信息.7':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(1,df7.shape[0]):\n",
    "        if df7.loc[i,'科目名称'] in list(df_qiong1['科目名称.7']):\n",
    "            index1 = list(df_qiong1['科目名称.7']).index(df7.loc[i,'科目名称'])\n",
    "            df7.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(1,df7.shape[0]):\n",
    "        if df7.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df7.loc[i,'科目代码'])\n",
    "            df7.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "    #生成表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash3(df7,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash3(df7,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash3(df7,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash3(df7,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash3(df7,df_final1,'科目代码','基金科目名称','Fund_subject','成本','成本占净值%','市值','市值占净值%','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash4(df7,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[0]\n",
    "    df_final2['账本成本占比'] = data_wash4(df7,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[1]\n",
    "    df_final2['账本市值'] = data_wash4(df7,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[2]\n",
    "    df_final2['账本市值占比'] = data_wash4(df7,df_final2,'科目名称','账本科目名称','Account_name','成本','成本占净值%','市值','市值占净值%')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #生成结果表\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form7.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form9(df9,df_final1,df_final2,df_final3):\n",
    "    #生成表3\n",
    "    df_deal= df9[df9['科目编码'].str.startswith('1103')&(df9['单位成本']>0.0)]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目编码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['本币成本']\n",
    "    df_final3['资产成本占比']=df_deal['本币成本占净值比(%)']*0.01\n",
    "    df_final3['市值']=df_deal['本币市值']\n",
    "    df_final3['市值占比']=df_deal['本币市值占净值比(%)']*0.01\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='AB:AC',header=2).iloc[:16,:]\n",
    "    df_qiong1.dropna(inplace=True)\n",
    "    df_qiong1['代码.9'] =df_qiong1['代码.9'].astype('int32')\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='AB:AC',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'Unnamed: 27':\"科目代码\"},inplace=True,axis=1)\n",
    "    # 处理科目名称\n",
    "    for i in range(1,df9.shape[0]):\n",
    "        if df9.loc[i,'科目名称'] in list(df_qiong1['科目名称.9']):\n",
    "            index1 = list(df_qiong1['科目名称.9']).index(df9.loc[i,'科目名称'])\n",
    "            df9.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    tmp = pd.merge(qiong_std2,df_qiong2,left_on='index',right_on='index')\n",
    "    for i in range(df9.shape[0]): \n",
    "        if df9.loc[i,'科目编码'] in list(tmp['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df9.loc[i,'科目编码'])\n",
    "            df9.loc[i,'科目编码'] = list(tmp['Unnamed: 1'])[index1]\n",
    "    #生成表1\n",
    "    for i in range(df9['科目编码'].shape[0]):\n",
    "        if df9.loc[i,'科目编码'].strip() in list(df_final1['基金科目名称']['Fund_subject']):\n",
    "            if df9.loc[i,'科目编码'] in ['年初单位净值','今日单位净值','昨日单位净值','累计单位净值','日净值增长率','净值增长率']:\n",
    "                index1 = list(df_final1['基金科目名称']['Fund_subject']).index(df9.loc[i,'科目编码'])\n",
    "                df_final1.loc[index1,('基金成本','Fund_cost')] = df9.loc[i,'科目名称']\n",
    "            else:\n",
    "                index1 = list(df_final1['基金科目名称']['Fund_subject']).index(df9.loc[i,'科目编码'])\n",
    "                df_final1.loc[index1,('基金成本','Fund_cost')] = df9.loc[i,'本币成本']\n",
    "                df_final1.loc[index1,('基金市值','fund_NAV')] = df9.loc[i,'本币市值']\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash4(df9,df_final2,'科目名称','账本科目名称','Account_name','本币成本','本币成本占净值比(%)','本币市值','本币市值占净值比(%)')[0]\n",
    "    df_final2['账本成本占比'] = data_wash4(df9,df_final2,'科目名称','账本科目名称','Account_name','本币成本','本币成本占净值比(%)','本币市值','本币市值占净值比(%)')[1]\n",
    "    df_final2['账本市值'] = data_wash4(df9,df_final2,'科目名称','账本科目名称','Account_name','本币成本','本币成本占净值比(%)','本币市值','本币市值占净值比(%)')[2]\n",
    "    df_final2['账本市值占比'] = data_wash4(df9,df_final2,'科目名称','账本科目名称','Account_name','本币成本','本币成本占净值比(%)','本币市值','本币市值占净值比(%)')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #输出结果表\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form9.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form10(df10,df_final1,df_final2,df_final3):\n",
    "    #生成表1\n",
    "    df_deal= df10[df10['科目代码'].str.startswith('1103')&(df10['单位成本']>0.0)&(df10['科目代码'].str.find('面额')!=-1)]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目代码']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['成    本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占净值%']*0.01\n",
    "    df_final3['市值']=df_deal['市    值']\n",
    "    df_final3['市值占比']=df_deal['市值占净值%']*0.01\n",
    "    df_final3['数量']=df_deal['数    量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='AE:AF',header=2).iloc[:16,:]\n",
    "    df_qiong1.dropna(inplace=True)\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='AE:AF',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'Unnamed: 30':\"科目代码\"},inplace=True,axis=1)\n",
    "    df_qiong2 = df_qiong2.iloc[:,:2].dropna()\n",
    "    # 处理科目名称\n",
    "    for i in range(1,df10.shape[0]):\n",
    "        if df10.loc[i,'科目名称'] in list(df_qiong1['科目名称.10']):\n",
    "            index1 = list(df_qiong1['科目名称.10']).index(df10.loc[i,'科目名称'])\n",
    "            df10.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(1,df10.shape[0]):\n",
    "        if df10.loc[i,'科目代码'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df10.loc[i,'科目代码'])\n",
    "            df10.loc[i,'科目代码'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "    #生成表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash3(df10,df_final1,'科目代码','基金科目名称','Fund_subject','成    本','成本占净值%','市    值','市值占净值%','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash3(df10,df_final1,'科目代码','基金科目名称','Fund_subject','成    本','成本占净值%','市    值','市值占净值%','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash3(df10,df_final1,'科目代码','基金科目名称','Fund_subject','成    本','成本占净值%','市    值','市值占净值%','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash3(df10,df_final1,'科目代码','基金科目名称','Fund_subject','成    本','成本占净值%','市    值','市值占净值%','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash3(df10,df_final1,'科目代码','基金科目名称','Fund_subject','成    本','成本占净值%','市    值','市值占净值%','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash4(df10,df_final2,'科目名称','账本科目名称','Account_name','成    本','成本占净值%','市    值','市值占净值%')[0]\n",
    "    df_final2['账本成本占比'] = data_wash4(df10,df_final2,'科目名称','账本科目名称','Account_name','成    本','成本占净值%','市    值','市值占净值%')[1]\n",
    "    df_final2['账本市值'] = data_wash4(df10,df_final2,'科目名称','账本科目名称','Account_name','成    本','成本占净值%','市    值','市值占净值%')[2]\n",
    "    df_final2['账本市值占比'] = data_wash4(df10,df_final2,'科目名称','账本科目名称','Account_name','成    本','成本占净值%','市    值','市值占净值%')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #生成结果表\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form10.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_form11(df11,df_final1,df_final2,df_final3):\n",
    "    #生成表3\n",
    "    df_deal= df11[df11['科目号'].str.startswith('1103')&(df11['单位成本']>0.0)]\n",
    "    df_deal.reset_index(drop=True,inplace=True)\n",
    "    df_final3.iloc[:,:]=np.nan\n",
    "    df_final3.drop(index=range(df_deal.shape[0],df_final3.shape[0]),axis=0,inplace=True)\n",
    "    df_final3['Asset_start_1']=df_deal['科目号']\n",
    "    df_final3['资产科目名称']=df_deal['科目名称']\n",
    "    df_final3['资产成本']=df_deal['本币成本']\n",
    "    df_final3['资产成本占比']=df_deal['成本占净值比(%)']*0.01\n",
    "    df_final3['市值']=df_deal['本币市值']\n",
    "    df_final3['市值占比']=df_deal['市值占净值比(%)']*0.01\n",
    "    df_final3['数量']=df_deal['数量']\n",
    "    df_final3['单位成本']=df_deal['单位成本']\n",
    "    df_final3.fillna('NA',inplace=True)\n",
    "    df_final3 = df_final3.append({('Asset_start_1','Asset_start'):'Asset_End',('资产科目名称','Asset_name'):'NA',('资产成本','Asset_cost'):'NA',('资产成本占比','asset_cost_percent'):'NA',('市值','asset_NAV'):'NA',('市值占比','asset_NAV_percent'):'NA',('数量','asset_amount'):'NA',('单位成本','asset_unit_cost'):'NA',('行情','asset_unit_NAV'):'NA',('最终债务人','asset_issuer'):'NA',('资产类型','asset_type'):'NA',('资产代码','asset_code'):'NA'},ignore_index=True)\n",
    "    #读取穷举表对应1表，并修改为结果表项目名称\n",
    "    qiong_std1 = pd.read_excel(qiong_form,usecols='A:B',header=2).iloc[:16,:]\n",
    "    qiong_std2 = pd.read_excel(qiong_form,usecols='A:B',header=19)\n",
    "    qiong_std2 = qiong_std2.reset_index()\n",
    "    df_qiong1 = pd.read_excel(qiong_form,usecols='AH:AI',header=2).iloc[:16,:]\n",
    "    df_qiong1.dropna(inplace=True)\n",
    "    df_qiong2 = pd.read_excel(qiong_form,usecols='AH:AI',header=19)\n",
    "    df_qiong2 = df_qiong2.reset_index()\n",
    "    df_qiong2.rename({'Unnamed: 33':\"科目代码\"},inplace=True,axis=1)\n",
    "    df_qiong2 = df_qiong2.iloc[:,:2].dropna()\n",
    "    # 处理科目名称\n",
    "    for i in range(1,df11.shape[0]):\n",
    "        if df11.loc[i,'科目名称'] in list(df_qiong1['科目名称.11']):\n",
    "            index1 = list(df_qiong1['科目名称.11']).index(df11.loc[i,'科目名称'])\n",
    "            df11.loc[i,'科目名称'] = list(qiong_std1['科目名称'])[index1]\n",
    "    #处理科目代码\n",
    "    df_qiong2.dropna(inplace=True)\n",
    "    for i in range(1,df11.shape[0]):\n",
    "        if df11.loc[i,'科目号'] in list(df_qiong2['科目代码']):\n",
    "            index1 = list(df_qiong2['科目代码']).index(df11.loc[i,'科目号'])\n",
    "            df11.loc[i,'科目号'] = list(qiong_std2['Unnamed: 1'])[index1]\n",
    "    # 生成表1\n",
    "    df_final1.loc[:4,('基金成本','Fund_cost')]=data_wash3(df11,df_final1,'科目号','基金科目名称','Fund_subject','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)','科目名称')[0]\n",
    "    df_final1.loc[:4,('基金成本占比','fund_cost_percent')]=data_wash3(df11,df_final1,'科目号','基金科目名称','Fund_subject','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)','科目名称')[1]\n",
    "    df_final1.loc[:4,('基金市值','fund_NAV')]=data_wash3(df11,df_final1,'科目号','基金科目名称','Fund_subject','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)','科目名称')[2]\n",
    "    df_final1.loc[:4,('基金市值占比','fund_NAV_percent')]=data_wash3(df11,df_final1,'科目号','基金科目名称','Fund_subject','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)','科目名称')[3]\n",
    "    df_final1.loc[5:,('基金成本','Fund_cost')]=data_wash3(df11,df_final1,'科目号','基金科目名称','Fund_subject','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)','科目名称')[4]\n",
    "    df_final1.loc[5:,('基金成本占比','fund_cost_percent')]='NA'\n",
    "    df_final1.loc[5:,('基金市值','fund_NAV')]='NA'\n",
    "    df_final1.loc[5:,('基金市值占比','fund_NAV_percent')]='NA'\n",
    "    df_final1.fillna('NA',inplace=True)\n",
    "    #生成表2\n",
    "    df_final2['账本成本'] = data_wash4(df11,df_final2,'科目名称','账本科目名称','Account_name','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)')[0]\n",
    "    df_final2['账本成本占比'] = data_wash4(df11,df_final2,'科目名称','账本科目名称','Account_name','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)')[1]\n",
    "    df_final2['账本市值'] = data_wash4(df11,df_final2,'科目名称','账本科目名称','Account_name','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)')[2]\n",
    "    df_final2['账本市值占比'] = data_wash4(df11,df_final2,'科目名称','账本科目名称','Account_name','本币成本','成本占净值比(%)','本币市值','市值占净值比(%)')[3]\n",
    "    df_final2.iloc[:,6:12] = np.nan\n",
    "    df_final2.fillna('NA',inplace=True)\n",
    "    #生成结果集\n",
    "    app = xw.App(visible=False,add_book=False)\n",
    "    wb = app.books.add()\n",
    "    wb.sheets[0].range('A1').value = df_final1\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4}').value = df_final2\n",
    "    wb.sheets[0].range(f'A{df_final1.shape[0]+4+df_final2.shape[0]+3}').value = df_final3\n",
    "    wb.sheets[0].range('A:A').delete()\n",
    "    wb.sheets[0].range('A1').expand('table').autofit()\n",
    "    wb.save('res_form11.xlsx')\n",
    "    wb.close()\n",
    "    app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_form11(df11,df_final1,df_final2,df_final3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_form1(df1,df_final1,df_final2,df_final3)\n",
    "# df_form2(df2,df_final1,df_final2,df_final3)\n",
    "# df_form3(df3,df_final1,df_final2,df_final3)\n",
    "# df_form4(df4,df_final1,df_final2,df_final3)\n",
    "# df_form5(df5,df_final1,df_final2,df_final3)\n",
    "# df_form6(df6,df_final1,df_final2,df_final3)\n",
    "# df_form7(df7,df_final1,df_final2,df_final3)\n",
    "# df_form9(df9,df_final1,df_final2,df_final3)\n",
    "# df_form10(df10,df_final1,df_final2,df_final3)\n",
    "# df_form11(df11,df_final1,df_final2,df_final3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "6f8ee94c255eb1f45edb80e83721093c1db1e2ea85447c0854292673b957abb8"
  },
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit ('base': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
